<?php
class sppd_model extends CI_Model{ 

	function sppd_model()
	{
		parent::__construct();
	}
	function getListsppd($limit='',$offset=''){
			$menus='';
			$judul=$this->input->post('judul');
			$status=$this->session->userdata('STATUS');
			$addTag="";
			if($status!=0){
			$addTag="where t_pegawai.nik='".$this->session->userdata('NIK')."'";	
			}
			$query=$this->db->query("select t_sppd.status as status,t_sppd.id as sppdid,t_pegawai.id,t_pegawai.nik,t_pegawai.nama,m_jabatan.nama_jabatan,m_unit_kerja.nama_unit_kerja,m_cabang.nama_cabang from t_sppd
   		    LEFT JOIN t_pegawai ON t_pegawai.nik=t_sppd.nik
			LEFT JOIN m_jabatan ON t_pegawai.kd_jabatan=m_jabatan.kd_jabatan
            LEFT JOIN m_unit_kerja ON t_pegawai.kd_unit_kerja=m_unit_kerja.kd_unit_kerja
            LEFT JOIN m_cabang ON t_pegawai.kd_cabang=m_cabang.kd_cabang
			$addTag
			ORDER BY t_sppd.id DESC");
			 if ($query->num_rows() > 0) {
				foreach ($query->result() as $data) {
					$menus[]=$data;
				}
				return $menus;
			}

		}
	/* GET DAT APEGAWAI */
		function getDataPegawai($id=''){
			$query=$this->db->query("select t_pegawai.id,t_pegawai.nik,t_pegawai.kd_cabang,t_pegawai.kd_unit_kerja,t_pegawai.kd_jabatan,t_pegawai.nama,t_pegawai.kd_level,m_jabatan.nama_jabatan,m_unit_kerja.nama_unit_kerja,m_cabang.nama_cabang from t_pegawai
		    LEFT JOIN m_jabatan ON t_pegawai.kd_jabatan=m_jabatan.kd_jabatan
            LEFT JOIN m_unit_kerja ON t_pegawai.kd_unit_kerja=m_unit_kerja.kd_unit_kerja
            LEFT JOIN m_cabang ON t_pegawai.kd_cabang=m_cabang.kd_cabang 
			where t_pegawai.id='$id'");
			return $query->row();
		}
		function getDataPegawaibyNik($nik=''){
			$query=$this->db->query("select t_pegawai.id,t_pegawai.nik,t_pegawai.kd_cabang,t_pegawai.kd_unit_kerja,t_pegawai.kd_jabatan,t_pegawai.nama,t_pegawai.kd_level,m_jabatan.nama_jabatan,m_unit_kerja.nama_unit_kerja,m_cabang.nama_cabang from t_pegawai
		    LEFT JOIN m_jabatan ON t_pegawai.kd_jabatan=m_jabatan.kd_jabatan
            LEFT JOIN m_unit_kerja ON t_pegawai.kd_unit_kerja=m_unit_kerja.kd_unit_kerja
            LEFT JOIN m_cabang ON t_pegawai.kd_cabang=m_cabang.kd_cabang 
			where t_pegawai.nik='$nik'");
			return $query->row();
		}

	/* --- */
	function count_sppd($id=''){
		$jumlah='';
		$status=$this->session->userdata('STATUS');
		$addTag="";
		if($status!=0){
		$addTag="where t_sppd.nik='".$this->session->userdata('NIK')."'";	
		}		
		$query=$this->db->query("select count(1) as jumlah from t_sppd $addTag");
			 if ($query->num_rows() > 0) {
				foreach ($query->result() as $data) {
				$jumlah=$data->jumlah;
				}
				return $jumlah;
			}
	}
	 
	function detailPegawai($id=''){
		$table="<table class='table-bordered' style='width:100%'>";	
			$judul=$this->input->post('judul');
			$query=$this->db->query("select a.id,a.nik,a.nama,m_jabatan.nama_jabatan,m_unit_kerja.nama_unit_kerja,m_cabang.nama_cabang from t_pegawai a
		    LEFT JOIN m_jabatan ON a.kd_jabatan=m_jabatan.kd_jabatan
            LEFT JOIN m_unit_kerja ON a.kd_unit_kerja=m_unit_kerja.kd_unit_kerja
            LEFT JOIN m_cabang ON a.kd_cabang=m_cabang.kd_cabang 
			where a.id='$id'");
			 if ($query->num_rows() > 0) {
				foreach ($query->result() as $row) {
					$table.="<tr>";	
					$table.="<td colspan='3'><h3>Detail Pengambilan sppd</h3></td>";	
					$table.="</tr>";
					$table.="<tr>";	
					$table.="<td style='width:150px'>Nik Pegawai</td><td><span class='label label-important'>".$row->nik."</span></td>";
					$table.="</tr>";
					$table.="<tr>";	
					$table.="<td>Nama Pegawai</td><td><span class='label label-important'>".$row->nama."</span></td>";
					$table.="</tr>";	
					$table.="<tr>";	
					$table.="<td>Sisa sppd</td><td><span class='label label-important'>".$this->sppd_model->getSisasppd($row->nik)."&nbsp Hari</span></td>";
					$table.="</tr>";	
				}
			}
			$table.="</table>";	
			echo $table;
	}
	 
	function simpan(){
		$nik=$this->input->post('nik');
		$kd_level=$this->input->post('level');
		$kd_cabang=$this->input->post('cabang');
		$kd_jabatan=$this->input->post('jabatan');
		$kd_unit_kerja=$this->input->post('unit_kerja');
		$tgl_mulai=date('Y-m-d', strtotime($this->input->post('tgl_mulai')));
		$tgl_selesai=date('Y-m-d', strtotime($this->input->post('tgl_selesai')));
		$tgl_sk=date('Y-m-d', strtotime($this->input->post('tgl_sk')));
		$ket=$this->input->post('ket');
		
		$comboAsal=$this->input->post('comboAsal');
		$comboTujuan=$this->input->post('comboTujuan');
		$comboKendaraan=$this->input->post('comboKendaraan');
		$data=array(
	 	 'nik'=>$nik,
		 'kd_cabang'=>$kd_cabang,
		 'kd_jabatan'=>$kd_jabatan,
		 'kd_unit_kerja'=>$kd_unit_kerja,
		 'kd_level'=>$kd_level,
		 'status'=>'P',
		 'keterangan'=>$ket,
		 'tgl_mulai'=>$tgl_mulai,
		 'tgl_selesai'=>$tgl_selesai,
		 'tgl_sk'=>$tgl_sk,
		 'tgl_pengajuan'=>date('y-m-d'),
		 'dari'=>$comboAsal,
		 'sampai'=>$comboTujuan,
		 'id_kendaraan'=>$comboKendaraan,
		);
		$this->db->trans_start();
		$this->db->insert('t_sppd',$data);
		$this->db->trans_complete(); 
	}	
    
	function detail($id){
	$c="'";
	$table="<div   id='div_".$id."' ><table class='table-bordered' style='width:100%'>";	
	$q2=$this->db->query("select a.dari,a.sampai,a.approveby,a.id_kendaraan,m_kendaraan.nama_kendaraan,a.tgl_mulai,a.tgl_selesai,(SELECT DATEDIFF(a.tgl_selesai,a.tgl_mulai)) AS diffDate,t_pegawai.id,t_pegawai.nik,t_pegawai.kd_cabang,t_pegawai.kd_unit_kerja,t_pegawai.kd_jabatan,t_pegawai.nama,t_pegawai.kd_level,m_jabatan.nama_jabatan,m_unit_kerja.nama_unit_kerja,m_cabang.nama_cabang 
			,(select nama_kota from m_kota where id=a.dari) as kotasal,(select nama_kota from m_kota where id=a.sampai) as kotatujuan
			from t_sppd a
    		LEFT JOIN t_pegawai ON t_pegawai.nik=a.nik
			LEFT JOIN m_jabatan ON t_pegawai.kd_jabatan=m_jabatan.kd_jabatan
            LEFT JOIN m_unit_kerja ON t_pegawai.kd_unit_kerja=m_unit_kerja.kd_unit_kerja
            LEFT JOIN m_cabang ON t_pegawai.kd_cabang=m_cabang.kd_cabang 
			LEFT JOIN m_kendaraan ON m_kendaraan.id=a.id_kendaraan 
			where a.id='$id'");
			if ($q2->num_rows() > 0) {
			foreach ($q2->result() as $row) {
				$table.="<tr>";	
				$table.="<td colspan='3'><h3>Detail Pengambilan Sppd</h3></td>";	
				$table.="</tr>";
				$table.="<tr>";	
				$table.="<td style='width:150px'>Nik Pegawai</td><td><span class='label label-important'>".$row->nik."</span></td>";
				$table.="</tr>";
				$table.="<tr>";	
				$table.="<td>Nama Pegawai</td><td><span class='label label-important'>".$row->nama."</span></td>";
				$table.="</tr>";	
				$table.="<tr>";	
				$table.="<td>Tanggal Mulai sppd</td><td><span class='label label-important'>".date('d-F-Y', strtotime( $row->tgl_mulai))."</span></td>";
				$table.="</tr>";	
				$table.="<tr>";	
				$table.="<td>Tanggal Selesai sppd</td><td><span class='label label-important'>".date('d-F-Y', strtotime( $row->tgl_selesai))."</span></td>";
				$table.="</tr>";	
				$table.="<tr>";	
				$table.="<td>Lama sppd</td><td><span class='label label-important'>".$row->diffDate."&nbsp Hari</span></td>";
				$table.="</tr>";
				$table.="<tr>";	
				$table.="<td>Dari</td><td><span class='label label-important'>".$row->kotasal."&nbsp</span></td>";
				$table.="</tr>";
				$table.="<tr>";	
				$table.="<td>Tujuan</td><td><span class='label label-important'>".$row->kotatujuan."&nbsp</span></td>";
				$table.="</tr>";
				$table.="<tr>";	
				$table.="<td>Kendaraan</td><td><span class='label label-important'>".$row->nama_kendaraan."&nbsp</span></td>";
				$table.="</tr>";
				$table.="<tr>";	
				$table.="<td>Biaya</td><td><span  style='float:left;font-size:50px;height:40px;min-width:200px;padding-top:20px;'  class='btn blue'>".$this->cariDetailTotalBiaya($row->dari,$row->sampai,$row->id_kendaraan)."&nbsp</span></td>";
				$table.="</tr>";
				$table.=$this->getDetailApprovedby($row->approveby);
				$table.="<tr>";	
				$table.='<td colspan="2"> <a onClick="return print_div('.$c.'div_'.$id.$c.')" class="btn simptip-position-left" style="float:right" data-tooltip="Cetak Berdasarkan Surat"><i class="icon-print"></i>Cetak</a>&nbsp</td>';
				$table.="</tr>";			
			}
		}
		$table.="</table></div>";	
		echo $table;	
	}
	function cariDetailTotalBiaya($c1='',$c2='',$c3=''){
		$nominal=0;
		
		$q2=$this->db->query("select * from m_sppd_kota  where dari='$c1' and tujuan='$c2' and id_kendaraan='$c3' ");
				if ($q2->num_rows() > 0) {
				foreach ($q2->result() as $row) { $nominal=$row->nominal; } }	 
			return  'Rp.'.number_format($nominal);	
	}
	function getDetailApprovedby($nik=''){
		$table="";		 
		
		$query=$this->db->query("select * from t_pegawai
			LEFT JOIN m_jabatan ON t_pegawai.kd_jabatan=m_jabatan.kd_jabatan
            LEFT JOIN m_unit_kerja ON t_pegawai.kd_unit_kerja=m_unit_kerja.kd_unit_kerja
            LEFT JOIN m_cabang ON t_pegawai.kd_cabang=m_cabang.kd_cabang
			where t_pegawai.nik='$nik'");
			 if ($query->num_rows() > 0) {
				foreach ($query->result() as $data) {
					$table.="<tr>";	
					$table.="<td>Approve By </td><td><span class='label label-success'>".$data->nama."&nbsp</span></td>";
					$table.="</tr>";
					$table.="<tr>";	
					$table.="<td>Jabatan </td><td><span class='label label-success'>".$data->nama_jabatan."&nbsp</span></td>";
					$table.="</tr>";
					$table.="<tr>";	
					$table.="<td>Lokasi Kerja </td><td><span class='label label-success'>".$data->nama_cabang."&nbsp</span></td>";
					$table.="</tr>";
					$table.="<tr>";	
					$table.="<td>Unit Kerja </td><td><span class='label label-success'>".$data->nama_unit_kerja."&nbsp</span></td>";
					$table.="</tr>";
				}
				 return $table;
			}
	}
	function delete($id=''){
		$this->db->delete('artikel', array('id' => $id)); 
		echo"Sukses Hapus Data...";
	}
	function approval(){
		$id=$this->input->post('idsppd');
		$act=$this->input->post('ra');
		$nik=$this->session->userdata('NIK');		 
		$this->db->query("update t_sppd set status='$act' , approveby='$nik' where id='$id'");
 		echo "Sukses Merubah Data sppd";
		
	}
	function generateComboAsal($id=''){	
	$table="<select id='comboAsal' onChange='return cariTotalBiaya()' name='comboAsal'><option value=''>-Pilih Kota Asal-</option>";	
	$q2=$this->db->query("select * from m_kota");
			if ($q2->num_rows() > 0) {
			foreach ($q2->result() as $row) {
				$selected="";
				if($id==$row->id){
					$selected="selected='selected'";
				}
				$table.="<option ".$selected." value='".$row->id."'>".$row->nama_kota."</option>"; 
			}
		}
	$table.="</select>";
		return  $table;	
	}
	function generateComboTujuan($id=''){
		$table="<select id='comboTujuan' onChange='return cariTotalBiaya()' name='comboTujuan'><option value=''>-Pilih Kota Tujuan-</option>";	
		$q2=$this->db->query("select * from m_kota");
			if ($q2->num_rows() > 0) {
			foreach ($q2->result() as $row) {
				$selected="";
				if($id==$row->id){
					$selected="selected='selected'";
				}
				$table.="<option ".$selected."  value='".$row->id."'>".$row->nama_kota."</option>"; 
			}
		}
		$table.="</select>";
		return  $table;	
	}
	function generateComboKendaraan($id=''){
		$table="<select id='comboKendaraan' onChange='return cariTotalBiaya()' name='comboKendaraan'><option value=''>-Pilih Jenis Kendaraan-</option>";	
		$q2=$this->db->query("select *from m_kendaraan");
			if ($q2->num_rows() > 0) {
			foreach ($q2->result() as $row) {
				$selected="";
				if($id==$row->id){
					$selected="selected='selected'";
				}
				$table.="<option ".$selected."  value='".$row->id."'>".$row->nama_kendaraan."</option>"; 
			}
		}
		$table.="</select>";
		return  $table;	
	}
	
	function cariTotalBiaya(){
		$dari=$this->input->post('comboAsal');
		$tujuan=$this->input->post('comboTujuan');
		$kendaraan=$this->input->post('comboKendaraan');
		$nominal=0;
		$q2=$this->db->query("select * from m_sppd_kota  where dari='$dari' and tujuan='$tujuan' and id_kendaraan='$kendaraan' ");
				if ($q2->num_rows() > 0) {
				foreach ($q2->result() as $row) {
					 $nominal=$row->nominal;
				}
			}	 
			echo  'Rp.'.number_format($nominal);	
	}
}
// END RiskIssue_model Class

/* End of file RiskIssue_model.php */
/* Location: ./application/models/RiskIssue_model.php */
?>